CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."FNC_JUDGE_HEI_KIN_ASHI_UP_DOWN" (p_date       in varchar2,
                                                                p_stock_code in varchar2)
  return number is
  /* 判断某一只股票的平均K线是否从下跌趋势变为上涨趋势。如果是，则返回1，如果不是，则返回-1 */
  result number;
  -- 当天ha_close_price大于等于ha_open_price的股票的数量
  -- 变量名称v_today_hei_kin_ashi_up_down_number
  v_today_h_k_a_u_d_number number := 0;
  -- 前一天ha_close_price小于ha_open_price的股票的数量
  -- 变量名称v_yesterday_hei_kin_ashi_down_up_number
  v_yesterday_h_k_a_d_u_number number := 0;
begin
  begin
  
    -- 当天ha_close_priceda大于等于ha_open_price的股票的数量
    select count(*)
      into v_today_h_k_a_u_d_number
      from stock_transaction_data_all std
     where std.code_ = p_stock_code
       and std.date_ = to_date(p_date, 'yyyy-mm-dd')
       and std.ha_close_price > std.ha_open_price;
    -- 前一天ha_close_price小于等于ha_open_price的股票的数量
    select count(*)
      into v_yesterday_h_k_a_d_u_number
      from stock_transaction_data_all std
     where std.code_ = p_stock_code
       and std.date_ =
           (select b.date_
              from (select *
                      from stock_transaction_data_all std1
                     where std1.date_ < to_date(p_date, 'yyyy-mm-dd')
                       and std1.code_ = p_stock_code
                       and std1.ha_open_price != std1.ha_close_price
                     order by std1.date_ desc) b
             where rownum <= 1)
       and std.ha_close_price < std.ha_open_price;
  
  exception
    when no_data_found then
      DBMS_OUTPUT.put_line('代码为【' || p_stock_code || '】的股票，在日期【' || p_date || '】，
      没有当天ha_close_price大于等于ha_open_price的股票，或者没有前一天ha_close_price小于ha_open_price的股票');
    
      result := -1;
      return(result);
  end;

  -- 判断某一只股票的平均K线是否从下跌趋势变为上涨趋势
  if v_today_h_k_a_u_d_number > 0 and v_yesterday_h_k_a_d_u_number > 0 then
    result := 1;
    return(result);
  else
    result := -1;
    return(result);
  end if;
end FNC_JUDGE_HEI_KIN_ASHI_UP_DOWN;